package com.mairuide.exception_analysis.service;

import com.mairuide._frame.utils.Testtrans;
import com.mairuide.casemanage.service.CaseManageService;
import com.mairuide.entity.PageResult;
import com.mairuide.exception_analysis.pojo.*;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Description
 * @auther 刘中华
 * @create 2019-03-13 11:39
 */
@Service
public class ExceptionAnalysisService {
    @Autowired
    JdbcTemplate jdbcTemplate;

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    private CaseManageService caseManageService;

    /**
     * 规律的交易
     * @param prop
     * @param page
     * @param size
     * @return
     */
    public PageResult getlist(Map<String,String> prop,Integer page, Integer size){
        String whichTable = caseManageService.getWhichTable(prop.get("casemanage_id"));
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT ACCOUNT_RIVAL_CARD account_rival_card ,min(TO_CHAR(TRADE_DATE,'DD')) AS min_day,MAX(TO_CHAR(TRADE_DATE,'DD')) AS max_day,COUNT(DISTINCT TO_CHAR(TRADE_DATE,'YYYY-MM')) AS count ");
        sb.append(" FROM "+whichTable);
        sb.append(" WHERE 1=1 ");
        if (StringUtils.isNotBlank(prop.get("casemanage_id"))){
            sb.append(" AND CASEMANAGE_ID = '"+prop.get("casemanage_id")+"' ");
        }
        sb.append(" AND ACCOUNT_RIVAL_CARD is not null ");
        if (StringUtils.isNotBlank(prop.get("cardList"))){
        	sb.append(" AND ACCOUNT_BODY_CARD in ( "+prop.get("cardList")+") ");
        }

        sb.append(" GROUP BY ACCOUNT_RIVAL_CARD ");
        List<ExceptionAnalysis> maps = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<ExceptionAnalysis>(ExceptionAnalysis.class));
        //过滤条件
        Integer count = Integer.valueOf(prop.get("count"));
        Integer day = Integer.valueOf(prop.get("interval_day"));
        //key交易对手卡号，val对应的对象
        HashMap<String, ExceptionAnalysis> stringExceptionAnalysisHashMap = new HashMap<>();
        List<String> analysisList = maps.stream().filter(m -> {
            stringExceptionAnalysisHashMap.put(m.getAccount_rival_card(),m);
            Integer maxDay = m.getMax_day()==null?0:m.getMax_day();
            Integer minDay = m.getMin_day()==null?0:m.getMin_day();
            Integer interval_day = maxDay - minDay;
            return m.getCount() >= count && interval_day <= day;
        }).map(p -> "'"+p.getAccount_rival_card()+"'").collect(Collectors.toList());

        if(analysisList.size()==0){
            PageResult pageResult = new PageResult(0l, null);
            return pageResult;
        }

        StringBuffer sb2=new StringBuffer();
        sb2.append(" SELECT ACCOUNT_BODY account_body , ACCOUNT_BODY_CARD account_body_card,ACCOUNT_RIVAL account_rival,ACCOUNT_RIVAL_CARD account_rival_card,count(1) trade_count,ROUND( sum( abs( account_money )), 2 ) sum_account_money ");
        sb2.append(" FROM "+whichTable+" WHERE 1=1 ");
        if (StringUtils.isNotBlank(prop.get("casemanage_id"))){
            sb2.append(" AND CASEMANAGE_ID = '"+prop.get("casemanage_id")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("cardList"))){
            sb2.append(" AND ACCOUNT_BODY_CARD in ( "+prop.get("cardList")+ ") ");
        }
        if (StringUtils.isNotBlank(prop.get("minTradeMoney"))){
            sb2.append(" AND account_money >= '"+prop.get("minTradeMoney")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("maxTradeMoney"))){
            sb2.append(" AND account_money <= '"+prop.get("maxTradeMoney")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("minAccountMoney"))){
            sb2.append(" AND surplus_money >= '"+prop.get("minAccountMoney")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("maxAccountMoney"))){
            sb2.append(" AND surplus_money <= '"+prop.get("maxAccountMoney")+"' ");
        }
        if (StringUtils.isNotBlank( prop.get("beginTime"))) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + prop.get("beginTime") + "'");
        }
        if (StringUtils.isNotBlank( prop.get("endTime") )) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + prop.get("endTime") + "'");
        }
        sb2.append(" AND ACCOUNT_RIVAL_CARD in ("+StringUtils.substring(analysisList.toString(),1,analysisList.toString().length()-1)+")");
        sb2.append(" GROUP BY ACCOUNT_BODY,ACCOUNT_BODY_CARD,ACCOUNT_RIVAL,ACCOUNT_RIVAL_CARD " );
        if (StringUtils.isNotBlank(prop.get("prop"))&&StringUtils.isNotBlank(prop.get("order"))){
            sb2.append(" ORDER BY " + prop.get("prop") + " " + prop.get("order"));
        }else{
            sb2.append(" ORDER BY ACCOUNT_RIVAL ");
        }
        Query query = this.entityManager.createNativeQuery(sb2.toString());
        // 将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(ExceptionAnalysisDetail.class));
        List<ExceptionAnalysisDetail> maps2 = query.getResultList();
        // 设置分页
        query.setFirstResult((page - 1) * size);
        query.setMaxResults(size);
        // 获取查询结果集
        List<ExceptionAnalysisDetail> list = query.getResultList();
        for (int i = 0; i < list.size(); i++) {
            String account_rival_card = list.get(i).getAccount_rival_card();
            ExceptionAnalysis exceptionAnalysis = stringExceptionAnalysisHashMap.get(account_rival_card);
            Integer min_day = exceptionAnalysis.getMin_day();
            Integer max_day = exceptionAnalysis.getMax_day();
            list.get(i).setTime_slot(min_day+"号~"+max_day+"号");
        }
        PageResult pageResult = new PageResult(Long.valueOf(maps2.size()), list);
        return pageResult;
    }
    public PageResult getlist_easyui(ExceptionAnalysisVO vo){
        String whichTable = caseManageService.getWhichTable(vo.getCaseId());
        StringBuffer sb=new StringBuffer();
        sb.append(" SELECT ACCOUNT_RIVAL_CARD account_rival_card ,min(TO_CHAR(TRADE_DATE,'DD')) AS min_day,MAX(TO_CHAR(TRADE_DATE,'DD')) AS max_day,COUNT(DISTINCT TO_CHAR(TRADE_DATE,'YYYY-MM')) AS count ");
        sb.append(" FROM "+whichTable);
        sb.append(" WHERE 1=1 ");
        if (StringUtils.isNotBlank(vo.getCaseId())){
            sb.append(" AND CASEMANAGE_ID = '"+vo.getCaseId()+"' ");
        }
        sb.append(" AND ACCOUNT_RIVAL_CARD is not null ");
        if (StringUtils.isNotBlank(vo.getCardList())){
            sb.append(" AND ACCOUNT_BODY_CARD in ( "+vo.getCardList()+") ");
        }else{
            List<ExceptionAnalysisDetail> list = new ArrayList<>();
            PageResult pageResult = new PageResult(0l, list);
            return pageResult;
        }

        sb.append(" GROUP BY ACCOUNT_RIVAL_CARD ");
        List<ExceptionAnalysis> maps = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<ExceptionAnalysis>(ExceptionAnalysis.class));
        //过滤条件
        Integer count = Integer.valueOf(vo.getCount());
        Integer day = Integer.valueOf(vo.getInterval_day());
        //key交易对手卡号，val对应的对象
        HashMap<String, ExceptionAnalysis> stringExceptionAnalysisHashMap = new HashMap<>();
        List<String> analysisList = maps.stream().filter(m -> {
            stringExceptionAnalysisHashMap.put(m.getAccount_rival_card(),m);
            Integer maxDay = m.getMax_day()==null?0:m.getMax_day();
            Integer minDay = m.getMin_day()==null?0:m.getMin_day();
            Integer interval_day = maxDay - minDay;
            return m.getCount() >= count && interval_day <= day;
        }).map(p -> "'"+p.getAccount_rival_card()+"'").collect(Collectors.toList());

        if(analysisList.size()==0){
            List<ExceptionAnalysisDetail> list = new ArrayList<>();
            PageResult pageResult = new PageResult(0l, list);
            return pageResult;
        }

        StringBuffer sb2=new StringBuffer();
        sb2.append(" SELECT ACCOUNT_BODY account_body , ACCOUNT_BODY_CARD account_body_card,ACCOUNT_RIVAL account_rival,ACCOUNT_RIVAL_CARD account_rival_card,count(1) trade_count,ROUND( sum( abs( account_money )), 2 ) sum_account_money ");
        sb2.append(" FROM "+whichTable+" WHERE 1=1 ");
        if (StringUtils.isNotBlank(vo.getCaseId())){
            sb2.append(" AND CASEMANAGE_ID = '"+vo.getCaseId()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getCardList())){
            sb2.append(" AND ACCOUNT_BODY_CARD in ( "+vo.getCardList()+ ") ");
        }
        if (StringUtils.isNotBlank(vo.getMinTradeMoney())){
            sb2.append(" AND account_money >= '"+vo.getMinTradeMoney()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getMaxTradeMoney())){
            sb2.append(" AND account_money <= '"+vo.getMaxTradeMoney()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getMinAccountMoney())){
            sb2.append(" AND surplus_money >= '"+vo.getMinAccountMoney()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getMaxAccountMoney())){
            sb2.append(" AND surplus_money <= '"+vo.getMaxAccountMoney()+"' ");
        }
        if (StringUtils.isNotBlank( vo.getBeginTime())) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + vo.getBeginTime() + "'");
        }
        if (StringUtils.isNotBlank( vo.getEndTime() )) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + vo.getEndTime() + "'");
        }
        sb2.append(" AND ACCOUNT_RIVAL_CARD in ("+StringUtils.substring(analysisList.toString(),1,analysisList.toString().length()-1)+")");
        sb2.append(" GROUP BY ACCOUNT_BODY,ACCOUNT_BODY_CARD,ACCOUNT_RIVAL,ACCOUNT_RIVAL_CARD " );
        if (StringUtils.isNotBlank(vo.getSort())&&StringUtils.isNotBlank(vo.getOrder())){
            sb2.append(" ORDER BY " + vo.getSort() + " " + vo.getOrder());
        }else{
            sb2.append(" ORDER BY ACCOUNT_RIVAL ");
        }
        Query query = this.entityManager.createNativeQuery(sb2.toString());
        // 将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(ExceptionAnalysisDetail.class));
        List<ExceptionAnalysisDetail> maps2 = query.getResultList();
        // 设置分页
        query.setFirstResult((vo.getPage() - 1) * vo.getRows());
        query.setMaxResults(vo.getRows());
        // 获取查询结果集
        List<ExceptionAnalysisDetail> list = query.getResultList();
        for (int i = 0; i < list.size(); i++) {
            String account_rival_card = list.get(i).getAccount_rival_card();
            ExceptionAnalysis exceptionAnalysis = stringExceptionAnalysisHashMap.get(account_rival_card);
            Integer min_day = exceptionAnalysis.getMin_day();
            Integer max_day = exceptionAnalysis.getMax_day();
            list.get(i).setTime_slot(min_day+"号~"+max_day+"号");
        }
        PageResult pageResult = new PageResult(Long.valueOf(maps2.size()), list);
        return pageResult;
    }

    /**
     * 短时间内的存款  取款
     * @param prop
     * @param page
     * @param size
     * @return
     */
    public PageResult shortTimeTransaction(Map<String,String> prop,Integer page, Integer size){
        String whichTable = caseManageService.getWhichTable(prop.get("casemanage_id"));
        StringBuffer sb2=new StringBuffer();
        sb2.append(" SELECT TO_CHAR(TRADE_DATE,'YYYY-MM') month, ACCOUNT_BODY account_body , ACCOUNT_BODY_CARD account_body_card,ACCOUNT_RIVAL account_rival,ACCOUNT_RIVAL_CARD account_rival_card,ROUND(avg(abs(account_money)),2) avg_account_money,count(1) trade_count,ROUND( sum( abs( account_money )), 2 ) sum_account_money ");
        sb2.append(" FROM "+whichTable+" WHERE 1=1 ");
        if (StringUtils.isNotBlank(prop.get("casemanage_id"))){
            sb2.append(" AND CASEMANAGE_ID = '"+prop.get("casemanage_id")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("cardList"))){
            sb2.append(" AND ACCOUNT_BODY_CARD in ( "+prop.get("cardList")+ ") ");
        }
        if (StringUtils.isNotBlank(prop.get("minTradeMoney"))){
            sb2.append(" AND account_money >= '"+prop.get("minTradeMoney")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("maxTradeMoney"))){
            sb2.append(" AND account_money <= '"+prop.get("maxTradeMoney")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("minAccountMoney"))){
            sb2.append(" AND surplus_money >= '"+prop.get("minAccountMoney")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("maxAccountMoney"))){
            sb2.append(" AND surplus_money <= '"+prop.get("maxAccountMoney")+"' ");
        }
        if (StringUtils.isNotBlank( prop.get("beginTime"))) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + prop.get("beginTime") + "'");
        }
        if (StringUtils.isNotBlank( prop.get("endTime") )) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + prop.get("endTime") + "'");
        }
        if (StringUtils.isNotBlank( prop.get("sign") )) {
            sb2.append(" AND sign = " + prop.get("sign") );
        }
        sb2.append(" GROUP BY ACCOUNT_BODY,ACCOUNT_BODY_CARD,ACCOUNT_RIVAL,ACCOUNT_RIVAL_CARD,TO_CHAR(TRADE_DATE,'YYYY-MM') " );
        String c=StringUtils.isNotBlank(prop.get("count"))?prop.get("count"):"1";
        Integer count = Integer.valueOf(c);
        sb2.append(" HAVING count(account_rival_card)>= " + count);
        if (StringUtils.isNotBlank(prop.get("prop"))&&StringUtils.isNotBlank(prop.get("order"))){
            sb2.append(" ORDER BY " + prop.get("prop") + " " + prop.get("order"));
        }else{
            sb2.append(" ORDER BY account_rival_card , avg_account_money DESC  ");
        }
        Query query = this.entityManager.createNativeQuery(sb2.toString());
        // 将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(ExceptionAnalysisDetail2.class));
        List<ExceptionAnalysisDetail2> maps2 = query.getResultList();
        // 设置分页
        query.setFirstResult((page - 1) * size);
        query.setMaxResults(size);
        // 获取查询结果集
        List<ExceptionAnalysisDetail2> list = query.getResultList();
        PageResult pageResult = new PageResult(Long.valueOf(maps2.size()), list);
        return pageResult;
    }
    public PageResult shortTimeTransaction_easyui(ExceptionAnalysisVO vo){
        String whichTable = caseManageService.getWhichTable(vo.getCaseId());
        StringBuffer sb2=new StringBuffer();
        sb2.append(" SELECT TO_CHAR(TRADE_DATE,'YYYY-MM') month, ACCOUNT_BODY account_body , ACCOUNT_BODY_CARD account_body_card,ACCOUNT_RIVAL account_rival,ACCOUNT_RIVAL_CARD account_rival_card,ROUND(avg(abs(account_money)),2) avg_account_money,count(1) trade_count,ROUND( sum( abs( account_money )), 2 ) sum_account_money ");
        sb2.append(" FROM "+whichTable+" WHERE 1=1 ");
        if (StringUtils.isNotBlank(vo.getCaseId())){
            sb2.append(" AND CASEMANAGE_ID = '"+vo.getCaseId()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getCardList())){
            sb2.append(" AND ACCOUNT_BODY_CARD in ( "+vo.getCardList()+ ") ");
        }else{
            List<ExceptionAnalysisDetail2> list = new ArrayList<>();
            PageResult pageResult = new PageResult(0l, list);
            return pageResult;
        }
        if (StringUtils.isNotBlank(vo.getMinTradeMoney())){
            sb2.append(" AND account_money >= '"+vo.getMinTradeMoney()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getMaxTradeMoney())){
            sb2.append(" AND account_money <= '"+vo.getMaxTradeMoney()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getMinAccountMoney())){
            sb2.append(" AND surplus_money >= '"+vo.getMinAccountMoney()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getMaxAccountMoney())){
            sb2.append(" AND surplus_money <= '"+vo.getMaxAccountMoney()+"' ");
        }
        if (StringUtils.isNotBlank( vo.getBeginTime())) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" +vo.getBeginTime() + "'");
        }
        if (StringUtils.isNotBlank( vo.getEndTime()  )) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + vo.getEndTime()  + "'");
        }
        if (StringUtils.isNotBlank( vo.getSign() )) {
            sb2.append(" AND sign = " + vo.getSign() );
        }
        sb2.append(" GROUP BY ACCOUNT_BODY,ACCOUNT_BODY_CARD,ACCOUNT_RIVAL,ACCOUNT_RIVAL_CARD,TO_CHAR(TRADE_DATE,'YYYY-MM') " );
        String c=StringUtils.isNotBlank(vo.getCount())?vo.getCount():"1";
        Integer count = Integer.valueOf(c);
        sb2.append(" HAVING count(account_rival_card)>= " + count);
        if (StringUtils.isNotBlank(vo.getSort())&&StringUtils.isNotBlank(vo.getOrder())){
            sb2.append(" ORDER BY " + vo.getSort() + " " + vo.getOrder());
        }else{
            sb2.append(" ORDER BY account_rival_card , avg_account_money DESC  ");
        }
        Query query = this.entityManager.createNativeQuery(sb2.toString());
        // 将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(ExceptionAnalysisDetail2.class));
        List<ExceptionAnalysisDetail2> maps2 = query.getResultList();
        // 设置分页
        query.setFirstResult((vo.getPage() - 1) * vo.getRows());
        query.setMaxResults(vo.getRows());
        // 获取查询结果集
        List<ExceptionAnalysisDetail2> list = query.getResultList();
        PageResult pageResult = new PageResult(Long.valueOf(maps2.size()), list);
        return pageResult;
    }


    /**
     * 4-4.5w 交易 的分析
     * @param prop
     * @param page
     * @param size
     * @return
     */
    public PageResult yinHangTransaction(Map<String,String> prop,Integer page, Integer size){
        String whichTable = caseManageService.getWhichTable(prop.get("casemanage_id"));
        StringBuffer sb2=new StringBuffer();
        sb2.append(" SELECT  ACCOUNT_BODY account_body , ACCOUNT_BODY_CARD account_body_card,ACCOUNT_RIVAL account_rival,ACCOUNT_RIVAL_CARD account_rival_card,ROUND(avg(abs(account_money)),2) avg_account_money,count(1) trade_count,ROUND( sum( abs( account_money )), 2 ) sum_account_money ");
        sb2.append(" FROM "+whichTable+" WHERE 1=1 ");
        if (StringUtils.isNotBlank(prop.get("casemanage_id"))){
            sb2.append(" AND CASEMANAGE_ID = '"+prop.get("casemanage_id")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("cardList"))){
            sb2.append(" AND ACCOUNT_BODY_CARD in ( "+prop.get("cardList")+ ") ");
        }
        if (StringUtils.isNotBlank(prop.get("minTradeMoney"))){
            sb2.append(" AND account_money >= '"+prop.get("minTradeMoney")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("maxTradeMoney"))){
            sb2.append(" AND account_money < '"+prop.get("maxTradeMoney")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("minAccountMoney"))){
            sb2.append(" AND surplus_money >= '"+prop.get("minAccountMoney")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("maxAccountMoney"))){
            sb2.append(" AND surplus_money <= '"+prop.get("maxAccountMoney")+"' ");
        }
        if (StringUtils.isNotBlank( prop.get("beginTime"))) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + prop.get("beginTime") + "'");
        }
        if (StringUtils.isNotBlank( prop.get("endTime") )) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + prop.get("endTime") + "'");
        }
        //sb2.append(" AND account_money >=40000 AND account_money <= 50000");
        sb2.append(" GROUP BY ACCOUNT_BODY,ACCOUNT_BODY_CARD,ACCOUNT_RIVAL,ACCOUNT_RIVAL_CARD " );
        String c=StringUtils.isNotBlank(prop.get("count"))?prop.get("count"):"1";
        Integer count = Integer.valueOf(c);
        sb2.append(" HAVING count(account_rival_card)>= " + count);
        if (StringUtils.isNotBlank(prop.get("prop"))&&StringUtils.isNotBlank(prop.get("order"))){
            sb2.append(" ORDER BY " + prop.get("prop") + " " + prop.get("order"));
        }else{
            sb2.append(" ORDER BY account_rival_card , avg_account_money DESC  ");
        }
        Query query = this.entityManager.createNativeQuery(sb2.toString());
        // 将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(ExceptionAnalysisDetail3.class));
        List<ExceptionAnalysisDetail3> maps2 = query.getResultList();
        // 设置分页
        query.setFirstResult((page - 1) * size);
        query.setMaxResults(size);
        // 获取查询结果集
        List<ExceptionAnalysisDetail3> list = query.getResultList();
        PageResult pageResult = new PageResult(Long.valueOf(maps2.size()), list);
        return pageResult;
    }
    public PageResult yinHangTransaction_easyui(ExceptionAnalysisVO vo){
        String whichTable = caseManageService.getWhichTable(vo.getCaseId());
        StringBuffer sb2=new StringBuffer();
        sb2.append(" SELECT  ACCOUNT_BODY account_body , ACCOUNT_BODY_CARD account_body_card,ACCOUNT_RIVAL account_rival,ACCOUNT_RIVAL_CARD account_rival_card,ROUND(avg(abs(account_money)),2) avg_account_money,count(1) trade_count,ROUND( sum( abs( account_money )), 2 ) sum_account_money ");
        sb2.append(" FROM "+whichTable+" WHERE 1=1 ");
        if (StringUtils.isNotBlank(vo.getCaseId())){
            sb2.append(" AND CASEMANAGE_ID = '"+vo.getCaseId()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getCardList())){
            sb2.append(" AND ACCOUNT_BODY_CARD in ( "+vo.getCardList()+ ") ");
        }else{
            List<ExceptionAnalysisDetail3> list = new ArrayList<>();
            PageResult pageResult = new PageResult(0l, list);
            return pageResult;
        }
        if (StringUtils.isNotBlank(vo.getMinTradeMoney())){
            sb2.append(" AND account_money >= '"+vo.getMinTradeMoney()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getMaxTradeMoney())){
            sb2.append(" AND account_money < '"+vo.getMaxTradeMoney()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getMinAccountMoney())){
            sb2.append(" AND surplus_money >= '"+vo.getMinAccountMoney()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getMaxAccountMoney())){
            sb2.append(" AND surplus_money <= '"+vo.getMaxAccountMoney()+"' ");
        }
        if (StringUtils.isNotBlank( vo.getBeginTime())) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + vo.getBeginTime() + "'");
        }
        if (StringUtils.isNotBlank( vo.getEndTime() )) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + vo.getEndTime() + "'");
        }
        //sb2.append(" AND account_money >=40000 AND account_money <= 50000");
        sb2.append(" GROUP BY ACCOUNT_BODY,ACCOUNT_BODY_CARD,ACCOUNT_RIVAL,ACCOUNT_RIVAL_CARD " );
        String c=StringUtils.isNotBlank(vo.getCount())?vo.getCount():"1";
        Integer count = Integer.valueOf(c);
        sb2.append(" HAVING count(account_rival_card)>= " + count);
        if (StringUtils.isNotBlank(vo.getSort())&&StringUtils.isNotBlank(vo.getOrder())){
            sb2.append(" ORDER BY " + vo.getSort() + " " + vo.getOrder());
        }else{
            sb2.append(" ORDER BY account_rival_card , avg_account_money DESC  ");
        }
        Query query = this.entityManager.createNativeQuery(sb2.toString());
        // 将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(ExceptionAnalysisDetail3.class));
        List<ExceptionAnalysisDetail3> maps2 = query.getResultList();
        // 设置分页
        query.setFirstResult((vo.getPage() - 1) * vo.getRows());
        query.setMaxResults(vo.getRows());
        // 获取查询结果集
        List<ExceptionAnalysisDetail3> list = query.getResultList();
        PageResult pageResult = new PageResult(Long.valueOf(maps2.size()), list);
        return pageResult;
    }

    /**
     * post机 交易 的分析
     * @param prop
     * @param page
     * @param size
     * @return
     */
    public PageResult postTransaction(Map<String,String> prop,Integer page, Integer size){
        String whichTable = caseManageService.getWhichTable(prop.get("casemanage_id"));
        StringBuffer sb2=new StringBuffer();
        sb2.append(" SELECT  ACCOUNT_BODY account_body , ACCOUNT_BODY_CARD account_body_card,ACCOUNT_RIVAL account_rival,ACCOUNT_RIVAL_CARD account_rival_card,ROUND(avg(abs(account_money)),2) avg_account_money,count(1) trade_count,ROUND( sum( abs( account_money )), 2 ) sum_account_money  ");
        sb2.append(" FROM "+whichTable+" WHERE 1=1 ");
        if (StringUtils.isNotBlank(prop.get("casemanage_id"))){
            sb2.append(" AND CASEMANAGE_ID = '"+prop.get("casemanage_id")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("cardList"))){
            sb2.append(" AND ACCOUNT_BODY_CARD in ( "+prop.get("cardList")+ ") ");
        }
        if (StringUtils.isNotBlank(prop.get("minTradeMoney"))){
            sb2.append(" AND account_money >= '"+prop.get("minTradeMoney")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("maxTradeMoney"))){
            sb2.append(" AND account_money <= '"+prop.get("maxTradeMoney")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("minAccountMoney"))){
            sb2.append(" AND surplus_money >= '"+prop.get("minAccountMoney")+"' ");
        }
        if (StringUtils.isNotBlank(prop.get("maxAccountMoney"))){
            sb2.append(" AND surplus_money <= '"+prop.get("maxAccountMoney")+"' ");
        }
        if (StringUtils.isNotBlank( prop.get("beginTime"))) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + prop.get("beginTime") + "'");
        }
        if (StringUtils.isNotBlank( prop.get("endTime") )) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" + prop.get("endTime") + "'");
        }
        sb2.append(" AND content_abstract LIKE '%post机%' ");
        sb2.append(" GROUP BY ACCOUNT_BODY,ACCOUNT_BODY_CARD,ACCOUNT_RIVAL,ACCOUNT_RIVAL_CARD " );
        String c=StringUtils.isNotBlank(prop.get("count"))?prop.get("count"):"1";
        Integer count = Integer.valueOf(c);
        sb2.append(" HAVING count(account_rival_card)>= " + count);
        if (StringUtils.isNotBlank(prop.get("prop"))&&StringUtils.isNotBlank(prop.get("order"))){
            sb2.append(" ORDER BY " + prop.get("prop") + " " + prop.get("order"));
        }else{
            sb2.append(" ORDER BY account_rival_card , avg_account_money DESC  ");
        }
        Query query = this.entityManager.createNativeQuery(sb2.toString());
        // 将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(ExceptionAnalysisDetail3.class));
        List<ExceptionAnalysisDetail3> maps2 = query.getResultList();
        // 设置分页
        query.setFirstResult((page - 1) * size);
        query.setMaxResults(size);
        // 获取查询结果集
        List<ExceptionAnalysisDetail3> list = query.getResultList();
        PageResult pageResult = new PageResult(Long.valueOf(maps2.size()), list);
        return pageResult;
    }
    public PageResult postTransaction_easyui(ExceptionAnalysisVO vo){
        String whichTable = caseManageService.getWhichTable(vo.getCaseId());
        StringBuffer sb2=new StringBuffer();
        sb2.append(" SELECT  ACCOUNT_BODY account_body , ACCOUNT_BODY_CARD account_body_card,ACCOUNT_RIVAL account_rival,ACCOUNT_RIVAL_CARD account_rival_card,ROUND(avg(abs(account_money)),2) avg_account_money,count(1) trade_count,ROUND( sum( abs( account_money )), 2 ) sum_account_money  ");
        sb2.append(" FROM "+whichTable+" WHERE 1=1 ");
        if (StringUtils.isNotBlank(vo.getCaseId())){
            sb2.append(" AND CASEMANAGE_ID = '"+vo.getCaseId()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getCardList())){
            sb2.append(" AND ACCOUNT_BODY_CARD in ( "+vo.getCardList()+ ") ");
        }else{
            List<ExceptionAnalysisDetail3> list = new ArrayList<>();
            PageResult pageResult = new PageResult(0l, list);
            return pageResult;
        }
        if (StringUtils.isNotBlank(vo.getMinTradeMoney())){
            sb2.append(" AND account_money >= '"+vo.getMinTradeMoney()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getMaxTradeMoney())){
            sb2.append(" AND account_money <= '"+vo.getMaxTradeMoney()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getMinAccountMoney())){
            sb2.append(" AND surplus_money >= '"+vo.getMinAccountMoney()+"' ");
        }
        if (StringUtils.isNotBlank(vo.getMaxAccountMoney())){
            sb2.append(" AND surplus_money <= '"+vo.getMaxAccountMoney()+"' ");
        }
        if (StringUtils.isNotBlank( vo.getBeginTime())) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') >= '" + vo.getBeginTime() + "'");
        }
        if (StringUtils.isNotBlank( vo.getEndTime() )) {
            sb2.append(" AND to_char(TRADE_DATE,'yyyy-mm-dd') <= '" +vo.getEndTime()+ "'");
        }
        sb2.append(" AND content_abstract LIKE '%pos机%' ");
        sb2.append(" GROUP BY ACCOUNT_BODY,ACCOUNT_BODY_CARD,ACCOUNT_RIVAL,ACCOUNT_RIVAL_CARD " );
        String c=StringUtils.isNotBlank(vo.getCount())?vo.getCount():"1";
        Integer count = Integer.valueOf(c);
        sb2.append(" HAVING count(account_rival_card)>= " + count);
        if (StringUtils.isNotBlank(vo.getSort())&&StringUtils.isNotBlank(vo.getOrder())){
            sb2.append(" ORDER BY " + vo.getSort() + " " + vo.getOrder());
        }else{
            sb2.append(" ORDER BY account_rival_card , avg_account_money DESC  ");
        }
        Query query = this.entityManager.createNativeQuery(sb2.toString());
        // 将查询结果集转为Map
        query.unwrap(SQLQuery.class).setResultTransformer(new Testtrans(ExceptionAnalysisDetail3.class));
        List<ExceptionAnalysisDetail3> maps2 = query.getResultList();
        // 设置分页
        query.setFirstResult((vo.getPage() - 1) * vo.getRows());
        query.setMaxResults(vo.getRows());
        // 获取查询结果集
        List<ExceptionAnalysisDetail3> list = query.getResultList();
        PageResult pageResult = new PageResult(Long.valueOf(maps2.size()), list);
        return pageResult;
    }


    public static void main(String[] args){
        List<Integer> integers = Arrays.asList(1, 2, 4);
        String[] s = StringUtils.split("6230947200004795091_156_1", "_");
        System.out.println(Arrays.toString(s));
        System.out.println(StringUtils.contains("6230947200004795091","_"));
        //System.out.println(StringUtils.substring("6230947200004795091_156_1",0,integers.toString().length()-1) );
    }
}
